unit serverUtils;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, tcpbuffers, fpstructs, fposUtils, SQLite3db, Sqlite3DS, sqlite3,
  db, md5, zCompres,  hpdf, hpdf_types;

type

  { TSQLDataset }

  TSQLDataset = class(TSqlite3Dataset)
  public
    function GetLastInsertRowID: int64;
    function TableAsSQL: string;
    function ErrorCode: integer;
    function ErrorMessage: string;
  end;


  TClientTerminal = class(TObject)
  public
    LastSentID: integer;
    LastReceivedID: Integer;
  end;

var
  SYSTEMDB: TFilename;
  SALESDB: TFilename;
  EVENTSDB: TFilename;

var
  BufferFull: Boolean;

function GetProductList( fn: TFilename ): TStringlist;
procedure SendProductList(connection: TTCPConnection; socket: TTCPSocket);
procedure SendProfileList(connection: TTCPConnection; socket: TTCPSocket);
function GetProfileList( fn: TFilename ): TStringlist;
{procedure SendProfileRecord(connection: TTCPConnection; socket: TTCPSocket; fn: TFilename; productref: string);
procedure SendProductRecord(connection: TTCPConnection; socket: TTCPSocket; fn: TFilename; productref: string);
function UpdateProduct( fn: TFilename; aProduct: TfpProduct ): Boolean;
function GetNewProductID(fn: TFilename): string;  }
procedure TryRegisterTerminal( connection: TTCPConnection; systemdatafile: TFilename; terminaldata: TTCPBuffer );
procedure RegisterEvent( termid, staffid: integer; eventinfo: string; fn: TFilename );
procedure SendReplacementTable(connection: TTCPConnection; socket: TTCPSocket; fn, tbl: string);
procedure SendConfirm(connection: TTCPConnection; Socket: TTCPSocket);
function QuickQueryFloat(fn: TFilename; Tablename, SQL: string): Double;
function QuickQueryInt(fn: TFilename; Tablename, SQL: string): integer;
procedure TestSQL;
procedure SendUpdate(connection: TTCPConnection; socket: TTCPSocket; fn: string; updateref: integer);
procedure AddToUpdate( fn, SQL: string );
procedure CheckSalesData(connection: TTCPConnection; fn: string; TerminalID, lastSaleNum: integer; socket: TTCPSocket);
procedure CheckShiftData(connection: TTCPConnection; fn: string; mess: TTCPBuffer; lastshiftstart: Double);
procedure SendTimecheck( connection: TTCPConnection; Socket: TTCPSocket );

procedure GetQuery(connection: TTCPConnection; query: TTCPBuffer);
procedure GetReport(connection: TTCPConnection; query: TTCPBuffer);
procedure ReceiveFile(connection: TTCPConnection; databuffer: TTCPBuffer; DestFolder: string);

implementation

uses fpreports;


function GetProductList(fn: TFilename): TStringlist;
var
  DB: TSqlite3Dataset;
  SQL: String;
begin
  Result := nil;
  DB := TSqlite3Dataset.Create(nil);
  DB.FileName := fn;
  DB.TableName := 'Products';
  DB.Open;
  SQL := 'Select id, name from Products ORDER BY name;';
  DB.ExecSQL(SQL);
  DB.First;
  if not DB.EOF then Result := TStringList.Create;
  While not DB.EOF do
        begin
          Result.Add(DB.FieldByName('id').AsString + '|' + DB.FieldByName('name').AsString);
          DB.Next;
        end;
  DB.Close;
  DB.Free;
end;

procedure SendProductList(connection: TTCPConnection; socket: TTCPSocket);
var
  dataStream: TMemoryStream;
  ProductList: TStringList;
  header: tfpHeader;
  strlength: integer;
  strdata: string;
  x: Integer;
begin
  ProductList := GetProductList(SYSTEM_CONFIG);
  if ProductList<>nil then
    try
       dataStream := TMemoryStream.Create;

       header.TerminalID := 0;
       header.ID := HEADER_ID;
       header.MessageType := fpmSubmitConfigData;
       header.datalength := ProductList.Count;
       header.datatype := fpdProductList;
       dataStream.Write(header, SizeOf(header));
       for x := 0 to ProductList.Count-1 do
           begin
             strdata := ProductList[x];
             strlength := Length(strdata);
             dataStream.Write(strlength, SizeOf(strlength));
             dataStream.Write(strdata[1], Length(strdata));
             writeln(strdata);
           end;
       dataStream.Position := 0;
   //    connection.Send(dataStream.Memory^, dataStream.Size, socket);
       writeln ('Sent productlist');
    finally
      ProductList.Free;
      dataStream.Free;
    end;
end;

procedure SendConfirm(connection: TTCPConnection; Socket: TTCPSocket);
var
  sendbuf: TTCPSendBuffer;
begin
  sendbuf := TTCPSendBuffer.Create(nil, Socket);
  sendbuf.Header := NewHeader(SERVER_ID, fpmConfirm);
  connection.SendMessage(sendbuf);
  writeln('Confirm sent');
end;

function QuickQueryFloat(fn: TFilename; Tablename, SQL: string): Double;
var
  SQLDB: TSqlite3Dataset;
begin
  write('QuickQuery: ');
  writeln(SQL);
  Result := -1;
  SQLDB := TSqlite3Dataset.Create(nil);
  SQLDB.FileName := fn;
  SQLDB.TableName := Tablename;
  if SQLDB <> nil then
     begin
       {$IFDEF DEBUG} WriteLn('Result of QuickQuery: ', SQLDB.QuickQuery(SQL));       {$ENDIF}
       TryStrToFloat(SQLDB.QuickQuery(SQL), Result);
     end;
  SQLDB.Free;
end;

function QuickQueryInt(fn: TFilename; Tablename, SQL: string): integer;
var
  SQLDB: TSqlite3Dataset;
begin
  writeln('QuickQuery for integer');
  Result := -1;
  SQLDB := TSqlite3Dataset.Create(nil);
  SQLDB.FileName := fn;
  SQLDB.TableName := Tablename;
  if SQLDB <> nil then
     begin
       TryStrToInt(SQLDB.QuickQuery(SQL), Result);
     end;
  SQLDB.Free;
end;

procedure SendProfileList(connection: TTCPConnection; socket: TTCPSocket);
var
  dataStream: TMemoryStream;
  ProfileList: TStringList;
  header: tfpHeader;
  strlength: integer;
  strdata: string;
begin
{  ProfileList := GetProfileList(DEFAULT_PROFILE);
  if ProfileList<>nil then
    try
       dataStream := TMemoryStream.Create;

       header.ID := HEADER_ID;
       header.TerminalID := 0;
       header.MessageType := fpmSubmitConfigData;
       header.datalength := ProfileList.Count;
       header.datatype := fpdProfileList;
       dataStream.Write(header, SizeOf(header));
       for x := 0 to ProfileList.Count-1 do
           begin
             strdata := ProfileList[x];
             strlength := Length(strdata);
             dataStream.Write(strlength, SizeOf(strlength));
             dataStream.Write(strdata[1], Length(strdata));
             writeln(strdata);
           end;
       dataStream.Position := 0;
       connection.Send(dataStream.Memory^, dataStream.Size, socket);
       writeln ('Sent profilelist');
    finally
      ProfileList.Free;
      dataStream.Free;
    end;   }
end;

function GetProfileList(fn: TFilename): TStringlist;
begin
 { Result := TStringList.Create;
  Result.Add('default');    }
end;

{procedure SendProfileRecord(connection: TLTcp; socket: TLSocket; fn: TFilename;
  productref: string);
var
  DB: TSqlite3Dataset;
  aProfile: TfpProfile;
  aPage: TfpPage;
  aButton: TfpButton;
  datastream: TMemoryStream;
  header: tfpHeader;
  x: Integer;
begin
  DB := TSqlite3Dataset.Create(nil);
  writeln('retrieving product ref ', productref);
  DB.FileName := fn + '.profile';
  DB.TableName := 'Profile';
  DB.Open;
  if DB.RecordCount>0 then
     begin
       datastream := TMemoryStream.Create;
       header.ID := HEADER_ID;
       header.MessageType := fpmSubmitConfigData;
       header.TerminalID := SERVER_ID;
       header.datatype := fpdProfileRef;
       header.datalength := 1;
       datastream.Write(header, SizeOf(header));
       aProfile.name := fn;
       aProfile.description := DB.FieldByName('description').AsString;
       DB.Close;
       DB.TableName := 'Pages';
       DB.Open;
       aProfile.pagecount := DB.RecordCount;
       try
         datastream.Write(aProfile, SizeOf(aProfile));
         for x := 1 to aProfile.pagecount do
             begin
               aPage.id := DB.FieldByName('id').AsInteger;
               aPage.title := DB.FieldByName('title').AsString;
               datastream.Write(aPage, SizeOf(aPage));
             end;
         DB.Close;
         DB.TableName := 'Buttons';
         DB.Open;
         aProfile.buttoncount := DB.RecordCount;
         writeln(aProfile.buttoncount, ' buttons found');
         for x := 1 to aProfile.buttoncount do
             begin
               aButton.id := DB.FieldByName('buttonID').AsInteger;
               aButton.product := DB.FieldByName('itemID').AsInteger;
               aButton.top := DB.FieldByName('top').AsInteger;;
               aButton.left := DB.FieldByName('left').AsInteger;;
               aButton.width := DB.FieldByName('width').AsInteger;;
               aButton.height := DB.FieldByName('height').AsInteger;;
               aButton.pagenum := DB.FieldByName('pagenum').AsInteger;;
               aButton.visible := DB.FieldByName('visible').AsInteger;;
               datastream.Write(aPage, SizeOf(aPage));
             end;
         datastream.Position := SizeOf(header);
         datastream.Write(aProfile, SizeOf(aProfile));
         datastream.Position := 0;
         if connection.Send(datastream.Memory^, datastream.Size)<>datastream.Size
            then writeln('Error when sending profile data');
       finally
         datastream.Free;
         DB.Free;
       end;
     end;
end;               }

{procedure SendProductRecord(connection: TLTcp; socket: TLSocket; fn: TFilename;
  productref: string);
var
  DB: TSqlite3Dataset;
  SQL: String;
  aProduct: TfpProduct;
  datastream: TMemoryStream;
  header: tfpHeader;
begin
  DB := TSqlite3Dataset.Create(nil);
  writeln('retrieving product ref ', productref);
  DB.FileName := fn;
  DB.TableName := 'Products';
  DB.Filter := 'barcode=''' + productref + '''';
  DB.Filtered := true;
  DB.Open;
  if DB.Locate('barcode', productref, []) then
     begin
       aProduct.id := DB.FieldByName('productid').AsInteger;
       writeln('Sending details for product ', aProduct.id);
       aProduct.barcode := DB.FieldByName('barcode').AsString;
       aProduct.name := DB.FieldByName('name').AsString;
       aProduct.costprice := DB.FieldByName('costprice').AsFloat;
       aProduct.saleprice := DB.FieldByName('saleprice').AsFloat;
       aProduct.dept := DB.FieldByName('dept').AsInteger;
       aProduct.taxcode := DB.FieldByName('taxcode').AsInteger;
       aProduct.suppliercode := DB.FieldByName('suppliercode').AsInteger;
       aProduct.stocklevel := DB.FieldByName('stocklevel').AsInteger;
       aProduct.discount := DB.FieldByName('discount').AsInteger;
       datastream := TMemoryStream.Create;
       try
         header.ID := HEADER_ID;
         header.TerminalID := 0;
         header.MessageType := fpmSubmitConfigData;
         header.datatype := fpdProductRef;
         header.datalength := 1;
         datastream.Write(header, SizeOf(header));
         datastream.Write(aProduct, SizeOf(aProduct));
         datastream.Position := 0;
         connection.Send(datastream.Memory^, datastream.Size);
       finally
         datastream.Free;
       end;
     end;
  DB.Close;
  DB.Free;
end;  }

{function UpdateProduct( fn: TFilename; aProduct: TfpProduct): Boolean;
var
  SQLDB: TSQLite;
  SQL: String;
begin
  writeln('Update record');
  SQLDB := TSQLite.Create(fn);
  if SQLDB <> nil then
     begin
       SQL := 'UPDATE Products SET barcode="' + aProduct.barcode
                                   + '", name="' + aProduct.name
                                   + '", taxcode=' + IntToStr(aProduct.taxcode)
                                   + ', costprice=' + FloatToStr(aProduct.costprice)
                                   + ', saleprice=' + FloatToStr(aProduct.saleprice)
                                   + ' WHERE productid=' +IntToStr(aProduct.id) + ';';
       if not SQLDB.Query(SQL, nil) then writeln(SQLDB.LastErrorMessage);
     end;
  Result := SQLDB.LastError=0;
  SQLDB.Free;
end;    }

{function GetNewProductID(fn: TFilename): string;
var
  DB: TSqlite3Dataset;
  SQL: String;
  aProduct: TfpProduct;
  datastream: TMemoryStream;
  newID: Integer;
begin
  Result := '0';
  DB := TSqlite3Dataset.Create(nil);
  DB.FileName := fn;
  DB.TableName := 'Products';
  DB.Open;
  SQL := 'Select id from Products WHERE id<=''' + DEFAULT_MAX + ''' ORDER BY id;';
  writeln(SQL);
  DB.ExecSQL(SQL);
  writeln('Recordcount is ', DB.RecordCount);
  DB.Last;
  if DB.RecordCount > 0
     then newID := StrToInt(DB.FieldByName('id').AsString)+1
     else newID := StrToInt(DEFAULT_MIN);
  writeln('newID is ', newID);
  SQL := 'INSERT INTO Products (id, name, costprice, saleprice, taxcode) VALUES (''' + IntToStr(newID) + ''', ''New product'', 0.0, 0.0, 0);';
  DB.ExecSQL(SQL);
  writeln('ReturnCode is ', DB.ReturnCode);
  if DB.ReturnCode=101 then Result := IntToStr(newID);
  DB.Close;
  DB.Free;
end; }

procedure TryRegisterTerminal ( connection: TTCPConnection; systemdatafile: TFilename; terminaldata: TTCPBuffer ) ;
var
  DB: TSQLDataset;
  SQL: String;
  Screenwidth: Integer;
  ScreenHeight: integer;
  terminalname: string;
  buffer: TTCPSendBuffer;
  termnamelength: Integer;
begin
  with terminaldata do
       begin
         termnamelength := Header.datalength-Sizeof(Screenwidth)-SizeOf(ScreenHeight);
         SetLength(terminalname, termnamelength);
         Data.Read(terminalname[1], termnamelength);
         Data.Read(Screenwidth, SizeOf(Screenwidth));
         Data.Read(ScreenHeight, SizeOf(ScreenHeight));
       end;

  DB := TSQLDataset.Create(nil);
  DB.FileName := systemdatafile;
  DB.TableName := 'Terminals';
  DB.Open;
  SQL := 'INSERT INTO Terminals (name, profile, lastsale, datarevision, screenwidth, screenheight) '
         + 'VALUES ("' + terminalname + '", 0, 0, 0, ' + IntToStr(Screenwidth) + ', ' + IntToStr(ScreenHeight) + ');';
  writeln(SQL);
  DB.ExecSQL(SQL);
  if not DB.ReturnCode in [SQLITE_OK, SQLITE_DONE] then writeln(DB.ReturnString)
   else
     begin
       SendReplacementTable(connection, terminaldata.Socket, systemdatafile, 'Buttons');
       SendReplacementTable(connection, terminaldata.Socket, systemdatafile, 'Pages');
       SendReplacementTable(connection, terminaldata.Socket, systemdatafile, 'Departments');
       SendReplacementTable(connection, terminaldata.Socket, systemdatafile, 'Products');
       SendReplacementTable(connection, terminaldata.Socket, systemdatafile, 'Staff');
       SendReplacementTable(connection, terminaldata.Socket, systemdatafile, 'Discounts');
       {$IFDEF DEBUG} writeln('All tables buffered');     {$ENDIF}
       buffer := TTCPSendBuffer.Create(nil, terminaldata.Socket);
       buffer.Header := NewHeader(SERVER_ID, fpmSubmitConfigData, fpdTerminalID);
       buffer.AddData(DB.GetLastInsertRowID);
       connection.SendMessage(buffer);
     end;
  DB.Close;
  DB.Free;
end;

procedure RegisterEvent ( termid, staffid: integer; eventinfo: string; fn: TFilename ) ;
var
  SQLDB: TSQLite;
  SQL: String;
begin
  {$IFDEF DEBUG} writeln( FormatDateTime('hh:nn:ss ', Now), 'Record event: ' + eventinfo);   {$ENDIF}
  SQLDB := TSQLite.Create(fn);
  if SQLDB <> nil then
     begin
       SQL := 'INSERT INTO TerminalLog(eventtime, TerminalID, StaffID, event) '
              + 'VALUES(' + DateTimeToJulianDateTimeAsString(Now)
              + ', ' + IntToStr(termid)
              + ', ' + IntToStr(staffid)
              + ', "' + eventinfo + '");';
       if not SQLDB.Query(SQL, nil) then writeln(SQLDB.LastErrorMessage);
     end;
  SQLDB.Free;
end;

procedure SendReplacementTable ( connection: TTCPConnection; socket: TTCPSocket; fn,
  tbl: string ) ;
var
  DB: TSQLDataset;
  SQL: String;
  sendbuffer: TTCPSendBuffer;
begin
  DB := TSQLDataset.Create(nil);
  try
    DB.FileName := fn;
    DB.TableName := tbl;
    DB.Open;
    SQL := DB.TableAsSQL;
    if Length(SQL)>0 then
      begin
        sendbuffer := TTCPSendBuffer.Create(nil, socket);
        sendbuffer.Header := NewHeader(SERVER_ID, fpmSubmitConfigData, fpdSQLUpdate);
        sendbuffer.AddData(SQL);
        connection.SendMessage(sendbuffer);
      end;
  finally
    DB.Close;
    DB.Free;
  end;
end;

procedure TestSQL;
var
  DB: TSQLDataset;
  SQL: String;
begin
  DB := TSQLDataset.Create(nil);
  DB.FileName := SYSTEM_CONFIG;
  DB.TableName := 'Terminals';
  DB.Open;
  SQL := DB.TableAsSQL;
  writeln(SQL);
  DB.Close;
  DB.Free;
end;

procedure SendUpdate(connection: TTCPConnection; socket: TTCPSocket; fn: string; updateref: integer);
var
  sql: TStringList;
  header: tfpHeader;
  buffer: TTCPSendBuffer;
begin
  sql := TStringList.Create;
  if not FileExists(fn) then
    begin
      WriteLn('Unable to open update file ', fn);
      Exit;
    end;
  sql.LoadFromFile(fn);
  {$IFDEF DEBUG} writeln(sql.text); {$ENDIF}

  header := NewHeader(SERVER_ID, fpmSubmitConfigData, fpdSQLUpdate);
  if Length(sql.Text)>0 then
    begin
      sql.Add('UPDATE Terminal SET datarevision=' + IntToStr(updateref) + ' WHERE 1;');
      if ExtractFileName(fn)=UPDATE_CONFIG then  // update all connections except socket, which is Admin connection
        begin
          connection.connection.IterReset;
          while connection.Connection.IterNext do
            begin // while we have clients to echo to
              if connection.Connection.Iterator<>socket then  //do not send to Admin connection
                begin
                  {$IFDEF DEBUG} writeln('updating ', connection.Connection.Iterator.PeerAddress ); {$ENDIF}
                  buffer := TTCPSendBuffer.Create(nil, connection.Connection.Iterator);
                  buffer.Header := header;
                  buffer.AddData(sql.Text);
                  connection.SendMessage(buffer);
                end;
               RenameFile(fn, ExtractFilePath(fn) + IntToStr(updateref) + '.upd');
            end;
        end
      else
        begin     //update connection on socket only
          {$IFDEF DEBUG} writeln('updating ', socket.PeerAddress ); {$ENDIF}
          buffer := TTCPSendBuffer.Create(nil, socket);
          buffer.Header := header;
          buffer.AddData(sql.Text);
          connection.SendMessage(buffer);
        end;
    end;
end;

procedure AddToUpdate(fn, SQL: string);
var
  f: Text;
begin
  AssignFile(f, fn);
  if FileExists(fn)
     then Append(f)
     else Rewrite(f);
  Write(f, SQL);
  Close(f);
end;

procedure CheckSalesData(connection: TTCPConnection; fn: string; TerminalID,
  lastSaleNum: integer; socket: TTCPSocket);
var
  lastsalereceived: integer;
  buf: TTCPSendBuffer;
begin
  {$IFDEF DEBUG} writeln('CheckSalesData');   {$ENDIF}
  lastsalereceived := QuickQueryInt(SALESDB,
                                 'SaleSummary',
                                 'SELECT SaleID FROM SaleSummary WHERE TerminalID=' + IntToStr(TerminalID)
                                  + ' ORDER BY SaleID DESC LIMIT 1;');
  {$IFDEF DEBUG} writeln('Last salenum: ', lastSaleNum,' last sale received: ', lastsalereceived);      {$ENDIF}
  if lastsalereceived<lastSaleNum then
    begin
      buf := TTCPSendBuffer.Create(nil, socket);
      buf.Header := NewHeader(SERVER_ID, fpmRequestSalesData);
      buf.AddData(lastsalereceived);
      connection.SendMessage(buf);
    end;
  {$IFDEF DEBUG} writeln('Last salenum: ', lastSaleNum,' last sale received: ', lastsalereceived); {$ENDIF}
end;

procedure CheckShiftData(connection: TTCPConnection; fn: string;
  mess: TTCPBuffer; lastshiftstart: Double);
var
  lastshiftreceived: Double;
  buf: TTCPSendBuffer;
begin
  {$IFDEF DEBUG} writeln('CheckShiftData');  {$ENDIF}
  lastshiftreceived := QuickQueryFloat(SALESDB,
                                 'Shifts',
                                 'SELECT starttime FROM Shifts WHERE TerminalID=' + IntToStr(mess.Header.TerminalID)
                                  + ' ORDER BY starttime DESC LIMIT 1;');
  {$IFDEF DEBUG} writeln( 'Checking last shift is good: ', lastshiftstart, #32, lastshiftreceived);  {$ENDIF}
  if (lastshiftreceived>0) and (lastshiftreceived<lastshiftstart) then
    begin
      buf := TTCPSendBuffer.Create(nil, mess.Socket);
      buf.Header := NewHeader(SERVER_ID, fpmRequestShiftData);
      buf.AddData(lastshiftreceived);
      connection.SendMessage(buf);
    end;
end;

procedure SendTimecheck(connection: TTCPConnection; Socket: TTCPSocket);
var
  t: TDateTime;
  buf: TTCPSendBuffer;
begin
  t := Now;
  buf := TTCPSendBuffer.Create(nil, Socket);
  buf.Header := NewHeader(SERVER_ID, fpmTimecheck);
  buf.AddData(t);
  writeln('Sending timecheck to ', Socket.PeerAddress);
  connection.SendMessage(buf);

end;

procedure GetQuery ( connection: TTCPConnection; query: TTCPBuffer) ;
var
  DB: TSQLDataset;
  SQL: String;
  SendBuffer: TTCPSendBuffer;
  err: Integer;
  fieldcount: integer;
  recordcount: integer;
  datalength: integer;
  s: TStringList;
  x: Integer;
  errmess: String;
begin
  errmess := '';
  DB := TSQLDataset.Create(nil);
  S := TStringList.Create;
  try
  DB.FileName := SYSTEMDB;
  DB.ExecSQL('ATTACH "' + SALESDB + '" AS sales;' );
  DB.ExecSQL('ATTACH "' + EVENTSDB + '" AS events;' );
  DB.TableName := 'Terminals';
  SetLength(SQL, query.Header.datalength);
  query.Data.Seek(SizeOf(query.Header), soFromBeginning);
  query.Data.Read(SQL[1], query.Header.datalength);
  DB.SQL := SQL;
  writeln(SQL);
  try
  DB.Open;
  except
  on E: Exception do
    begin
      writeln('Error trapped');
      errmess := DB.ErrorMessage;
      writeln(errmess);
    end;
  end;
  err := DB.ErrorCode;
  writeln('DB.Errorcode=', err);
  fieldcount := 0;
  recordcount := 0;
  datalength := 0;
  if err=0 then
    begin
      if not DB.IsEmpty then
          begin
            fieldcount := DB.FieldCount;
            recordcount := DB.RecordCount;
            for x := 0 to DB.FieldCount-1 do
              S.Add(DB.Fields[x].FieldName);
            DB.First;
            while not DB.EOF do
              begin
                for x := 0 to DB.FieldCount-1 do
                  S.Add( EscapeLineEnding(DB.Fields[x].AsString) );
                DB.Next;
              end;
          end;
      datalength := Length(S.Text);
    end
  else
    begin
      datalength := Length(errmess);
      S.Add(errmess);
    end;
  SendBuffer := TTCPSendBuffer.Create(nil, query.Socket);
  SendBuffer.Header := NewHeader(SERVER_ID, fpmQueryResult);
  SendBuffer.AddData(err);
  SendBuffer.AddData(fieldcount);
  SendBuffer.AddData(recordcount);
  SendBuffer.AddData(datalength);
  SendBuffer.AddData(S.Text);
  connection.SendMessage(SendBuffer);

  finally
    DB.Free;
    S.Free;
    query.MessageComplete := true;
  end;
end;

procedure GetReport ( connection: TTCPConnection; query: TTCPBuffer ) ;
var
  pdf: HPDF_Doc;
  docsize: Integer;
  header: tfpHeader;
  SendBuffer: TTCPSendBuffer;
  buf: TByteArray;
  TerminalID: Integer;
  ItemID: Integer;
  ln: integer;
  checksum: string;
begin
  pdf := nil;
  if query.Header.datatype=fpdShiftReport then
    begin
      query.Data.Seek(SizeOf(header), soFromBeginning);
      query.Data.Read(TerminalID, SizeOf(TerminalID));
      query.Data.Read(ln, SizeOf(ln));
      SetLength(checksum, ln);
      query.Data.Read(checksum[1], ln);
      pdf := HPDF_New(nil, nil);
      HPDF_SetInfoAttr(pdf, HPDF_INFO_CREATOR, PChar(FIREPOS_INFO));
      HPDF_SetInfoDateAttr(pdf, HPDF_INFO_CREATION_DATE, HPDF_CurrentTime);
      HPDF_SetCompressionMode (pdf, $0F);
      docsize := ShiftReport(pdf, TerminalID, checksum);
    end
  else if query.Header.datatype=fpdItemSalesReport then
    begin
      query.Data.Seek(SizeOf(header), soFromBeginning);
      query.Data.Read(ItemID, SizeOf(ItemID));
      pdf := HPDF_New(nil, nil);
      HPDF_SetInfoAttr(pdf, HPDF_INFO_CREATOR, PChar(FIREPOS_INFO));
      HPDF_SetInfoDateAttr(pdf, HPDF_INFO_CREATION_DATE, HPDF_CurrentTime);
      HPDF_SetCompressionMode (pdf, $0F);
      docsize := ItemSalesReport(pdf, ItemID);
    end;

  if docsize>0 then
    begin
      SendBuffer := TTCPSendBuffer.Create(nil, query.Socket);
      SendBuffer.Header := NewHeader(SERVER_ID, fpmPDFReport);
      HPDF_ReadFromStream(pdf, HPDF_PBYTE( @buf ), @docsize);
      SendBuffer.AddData(buf, docsize);
      connection.SendMessage(SendBuffer);
      writeln('PDF report sent');
    end;
  if pdf<>nil then HPDF_FreeDocAll(pdf);
end;

procedure ReceiveFile ( connection: TTCPConnection; databuffer: TTCPBuffer; DestFolder: string) ;
var
  datalength: integer;
  filename: string;
  header: tfpHeader;
  FS: TMemoryStream;
begin
  databuffer.Data.Seek(SizeOf(header), soFromBeginning);
  databuffer.Data.Read(datalength, SizeOf(datalength)); // length of filename
  SetLength(filename, datalength);
  databuffer.Data.Read(filename[1], datalength);
  databuffer.Data.Read(datalength, SizeOf(datalength)); // length of file content
  FS := TMemoryStream.Create;
  try
    FS.CopyFrom(databuffer.Data, datalength);
    ForceDirectories(DestFolder);
    FS.SaveToFile(DestFolder + filename);
  finally
    FS.Free;
  end;

end;

{ TSQLDataset }

function TSQLDataset.GetLastInsertRowID: int64;
begin
    Result:=sqlite3_last_insert_rowid(FSqliteHandle);
end;

function TSQLDataset.TableAsSQL: string;
var
  Cols: String;
  c: Integer;
  DeleteSQL: String;
  InsertSQL: String;
  f: String;
begin
  Result := '';
  InsertSQL := '';
  // delete old table and get current table description
  DeleteSQL := 'DROP TABLE IF EXISTS ' + TableName + ';' + LineEnding;
  DeleteSQL := DeleteSQL + QuickQuery('SELECT sql from sqlite_master WHERE type=''table'' AND tbl_name=''' + TableName + ''';', nil )+ ';' + LineEnding;
  First;
  While not EOF do
    begin
      Cols := 'INSERT INTO ' + TableName + ' (';
      for c := 0 to FieldCount-1 do
          Cols := Cols + Fields[c].FieldName + ',';
      Cols[Length(Cols)] := ')';
      Cols := Cols + ' VALUES (';
      for c := 0 to FieldCount-1 do
        begin
          f := Fields[c].AsString;
          if Length(f)=0
             then Cols := Cols + 'NULL,'
          else if Fields[c].DataType in [ftString, ftWideString, ftMemo]
             then Cols := Cols + '''' + EscapeQuotes( f ) + '''' + ','
          else Cols := Cols + f + ',';
        end;
      Cols[Length(Cols)] := ')';
      Cols := Cols + ';' + LineEnding;
      InsertSQL := InsertSQL + Cols;
      Next;
    end;
  Result := DeleteSQL;
  if Length(InsertSQL)>0 then // table is not empty
     Result := Result + InsertSQL;
end;

function TSQLDataset.ErrorCode: integer;
begin
  Result := sqlite3_errcode(FSqliteHandle);
end;

function TSQLDataset.ErrorMessage: string;
begin
  Result := sqlite3_errmsg(FSqliteHandle);
end;

end.

